package com.cgnpc.scp.common.service;

import com.cgnpc.scp.common.bean.CreateFileInfo;
import com.cgnpc.scp.common.bean.ExcelCustomResultHandler;
import com.cgnpc.scp.common.exception.ScpException;
import com.cgnpc.scp.common.utils.DateUtil;
import com.cgnpc.scp.common.utils.ExcelUtil;
import com.cgnpc.scp.common.utils.FileUtil;
import com.cgnpc.scp.common.utils.IOUtil;
import com.cgnpc.scp.common.utils.ListUtil;
import com.cgnpc.scp.common.utils.MybatisUtil;
import com.cgnpc.scp.common.utils.StringUtil;
import com.cgnpc.scp.manager.mybatis.entity.DataDict;
import com.cgnpc.scp.manager.service.IDataDictService;
import org.apache.commons.beanutils.BeanUtilsBean2;
import org.apache.commons.beanutils.PropertyUtilsBean;
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.SqlSession;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * Excel的导出服务
 */
@Service
public class ExcelCustomTemplateService {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelCustomTemplateService.class);

    /**
     * Excel导出定制的前缀
     */
    public static final String PREFIX_CUSTOM_EXCEL_EXPORT_WITH_MYBATIS = "CustomExcelExportWithMyBatis";

    @Autowired
    private IDataDictService dataDictService;

    @Autowired
    private AppConstantService appConstantService;

    /**
     * 创建xlsx文件
     *
     * @param sqlSession               mybatis.SqlSession，用来执行mybatis创建的查询结构
     * @param mappedStatementId        mybatis.MapperXML文件的namespace + "." + selectId<br>
     *                                 例如：文件：UserMapper.xml；<br>
     *                                 namespace：com.xxx.user.mybatis.mapper.UserMapper；<br>
     *                                 select节点的id: selectByExample<br>
     *                                 最后的值为：com.xxx.user.mybatis.mapper.UserMapper.selectByExample<br>
     * @param mappedStatementParameter mapper.xml的select查询参数parameterType
     * @param webLocalFile             文件生成的地方，一般为服务器的实际目录
     * @param excelResultHandler       handler
     * @throws Exception exception
     */
    public static void createXlsxToWebDir(
            SqlSession sqlSession,
            String mappedStatementId,
            Object mappedStatementParameter,
            File webLocalFile,
            ExcelCustomResultHandler excelResultHandler
    ) throws Exception {
        final String logInfo = "[createXlsxToWebDir_UUID_"
                + UUID.randomUUID().toString().replace("-", "")
                + "]";

        SXSSFWorkbook workbook = null;
        FileOutputStream fileOut = null;

        final long longBeginTime = System.currentTimeMillis();

        try {
            LOGGER.info("{}...begin...", logInfo);
            LOGGER.info("{}printInfo;"
                            + "longBeginTime:{};webLocalFile:{};mappedStatementId:{};"
                            + "mappedStatementParameter:{};"
                    , logInfo
                    , longBeginTime, webLocalFile, mappedStatementId
                    , mappedStatementParameter
            );

            if (webLocalFile == null) {
                LOGGER.error("{}webLocalFile is null;", logInfo);
                return;
            }

            // keep 100 rows in memory, exceeding rows will be flushed to disk
            final SXSSFWorkbook workbookRef = workbook = new SXSSFWorkbook(ExcelUtil.WB_WINDOWSIZE);

            // 不压缩临时文件，提升速度
            // workbookRef.setCompressTempFiles(true); // temp files will be gzipped
            workbookRef.setCompressTempFiles(false); // 不需要压缩临时文件，会提升导出速度

            // 必须创建一个sheet，因为xlsx的文件必须存在一个sheet，否则打开报错；
            // final Sheet[] arrSheet = {workbookRef.createSheet()};

            final long longQueryDbBeginTime = System.currentTimeMillis();
            final AtomicInteger queryIndex = new AtomicInteger();

            ResultHandler mybatisResultHandler = new ResultHandler() {
                @Override
                public void handleResult(ResultContext resultContext) {
                    if (queryIndex.get() <= 0) {
                        queryIndex.getAndIncrement();

                        long longQueryDbEndTime = System.currentTimeMillis();
                        LOGGER.info("{}queryDb;usedTime: {} seconds;"
                                , logInfo
                                , (longQueryDbEndTime - longQueryDbBeginTime) / 1000d
                        );
                    }

                    // int resultCount = resultContext.getResultCount();
                    // Object resultObject = resultContext.getResultObject(); // 数据库结果集的一行数据
                    excelResultHandler.handleResult(resultContext, workbookRef);
                }
            };

            // 指定一个全新的
            String mappedStatementUsedId = PREFIX_CUSTOM_EXCEL_EXPORT_WITH_MYBATIS + "." + mappedStatementId;
            ExcelTemplateService.setSqlSession(mappedStatementUsedId, sqlSession, mappedStatementId);
            sqlSession.select(mappedStatementUsedId, mappedStatementParameter, mybatisResultHandler);

            fileOut = new FileOutputStream(webLocalFile);
            workbookRef.write(fileOut);
        } catch (Exception e) {
            LOGGER.error("{}error;", logInfo, e);
            throw e;
        } finally {
            MybatisUtil.closeSqlSession(sqlSession);
            ExcelUtil.closeWorkbook(workbook);
            IOUtil.closeStream(fileOut);

            final long longEndTime = System.currentTimeMillis();

            LOGGER.info("{}...end...usedTime: {} seconds;"
                    , logInfo
                    , (longEndTime - longBeginTime) / 1000d
            );
        }
    }

    public void testCreateXlsxToWebDir() throws Exception {
        SqlSession sqlSession = null;
        // mappedStatementNamespace为mybatis生成的mapper.xml中的命名空间
        String mappedStatementNamespace = "com.nuotu.catalogue.mybatis.mapper.BookCatalogueMapper";
        // mappedStatementMethod为mybatis生成的mapper.xml中的方法
        String mappedStatementMethod = "selectByExample";
        // mappedStatementId为具体指向的方法
        String mappedStatementId = mappedStatementNamespace + "." + mappedStatementMethod;
        String strFile = "D:/_testExcelExport/test_excel_export_" + System.currentTimeMillis() + "-" + UUID.randomUUID().toString() + ".xlsx";
        File webLocalFile = new File(strFile);

        // mappedStatementParameter可以为mybatis自动生成的XxxExample实体类，例如UserExample
        Object mappedStatementParameter = null;

        // 创建多层目录
        FileUtil.mkdirs(webLocalFile.getParentFile());

        final AtomicInteger rowIndex = new AtomicInteger();
        final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        final Sheet[] arrSheet = {null};
        // 每个Excel的CellStyle是有数量限制的，尽量使用相同的CellStyle
        final CellStyle[] arrCellStyleTitle = {null};

        final List<String> titleList = Arrays.asList(
                "标题-001", "标题-002", "标题-003"
        );

        ExcelCustomResultHandler<Object> excelResultHandler = new ExcelCustomResultHandler<Object>() {
            @Override
            public void handleResult(ResultContext<Object> resultContext, SXSSFWorkbook workbook) {
                // resultCount
                // int resultCount = resultContext.getResultCount();
                // resultObject每一行数据
                Object resultObject = resultContext.getResultObject();

                if (arrSheet[0] == null) {
                    // 创建sheet
                    arrSheet[0] = workbook.createSheet();
                    // 创建row
                    Row row = arrSheet[0].createRow(rowIndex.getAndIncrement());
                    Cell cell = null;

                    if (arrCellStyleTitle[0] == null) {
                        // begin 创建字体
                        Font fontTitle = workbook.createFont();
                        // make it blue
                        fontTitle.setColor(Font.COLOR_RED);
                        // end 创建字体

                        // begin 创建单元格样式
                        CellStyle cellStyleTitle = workbook.createCellStyle();
                        cellStyleTitle.setFont(fontTitle);
                        // end 创建单元格样式

                        arrCellStyleTitle[0] = cellStyleTitle;
                    }

                    // 创建并设置cell
                    for (int aaa = 0; aaa < titleList.size(); aaa++) {
                        cell = row.createCell(aaa);
                        // 设置单元格样式
                        if (arrCellStyleTitle[0] != null) {
                            cell.setCellStyle(arrCellStyleTitle[0]);
                        }

                        ExcelUtil.setCellValue(cell, titleList.get(aaa));
                    }
                }

                Date testDate = new Date();

                // 创建数据数组，元素应该对应titleList
                List<Object> dataList = Arrays.asList(
                        "内容-001",
                        DateUtil.formatDate(sdf, testDate),
                        resultObject.toString()
                );

                // 创建row
                Row row = arrSheet[0].createRow(rowIndex.getAndIncrement());
                Cell cell = null;

                // 创建并设置cell
                for (int aaa = 0; aaa < dataList.size(); aaa++) {
                    cell = row.createCell(aaa);
                    ExcelUtil.setCellValue(cell, dataList.get(aaa));
                }
            }
        };

        ExcelCustomTemplateService.createXlsxToWebDir(
                sqlSession,
                mappedStatementId,
                mappedStatementParameter,
                webLocalFile,
                excelResultHandler
        );
    }

    /**
     * 创建xlsx文件
     *
     * @param sqlSession               mybatis.SqlSession，用来执行mybatis创建的查询结构
     * @param mappedStatementId        mybatis.MapperXML文件的namespace + "." + selectId<br>
     *                                 例如：文件：UserMapper.xml；<br>
     *                                 namespace：com.xxx.user.mybatis.mapper.UserMapper；<br>
     *                                 select节点的id: selectByExample<br>
     *                                 最后的值为：com.xxx.user.mybatis.mapper.UserMapper.selectByExample<br>
     * @param mappedStatementParameter mapper.xml的select查询参数parameterType
     * @param exportDictGroupKey       数据字典key
     * @throws Exception exception
     */
    public CreateFileInfo createXlsxToWebDir(
            SqlSession sqlSession,
            String mappedStatementId,
            Object mappedStatementParameter,
            String exportDictGroupKey
    ) throws Exception {
        final String logInfo = "[createXlsxToWebDir_UUID_" + StringUtil.getUuid32() + "]";
        CreateFileInfo createFileInfo = null;

        try {
            // begin 临时路径相关
            SimpleDateFormat sdfWebLocal = new SimpleDateFormat("yyyyMM");
            String strFileFull = sdfWebLocal.format(new Date()) + "/" + "excel_export_" + System.currentTimeMillis() + "-" + UUID.randomUUID().toString() + ".xlsx";
            String tempCreateFileDir = appConstantService.getTempCreateFileDir();
            File webLocalFile = new File(new File(tempCreateFileDir), strFileFull);

            // 创建多层目录
            FileUtil.mkdirs(webLocalFile.getParentFile());
            // end 临时路径相关

            // 创建返回对象
            createFileInfo = new CreateFileInfo();
            createFileInfo.setWebLocalFile(webLocalFile);
            createFileInfo.setWebRequestUrl(appConstantService.getWebTempFileRootUrl() + strFileFull);

            final AtomicInteger rowIndex = new AtomicInteger();
            final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            final Sheet[] arrSheet = {null};
            // 每个Excel的CellStyle是有数量限制的，尽量使用相同的CellStyle
            final CellStyle[] arrCellStyleTitle = {null};

            // final List<String> dataDictKeyList = new ArrayList<>();
            final List<String> titleList = new ArrayList<>();
            final List<String> formatDictGroupKeyList = new ArrayList<>();
            final Map<String, Map<String, DataDict>> formatDictGroupKeyWithDictKeyMap = new HashMap<>();

            // begin 解析导出的数据字典信息

            List<DataDict> exportDataDictList = dataDictService.queryByDictGroupKey(Arrays.asList(exportDictGroupKey));

            if (ListUtil.getListSizeWith0(exportDataDictList) > 0) {
                // String exportDictKey = null;
                String exportDictText = null;
                String exportFormatDictGroupKey = null;

                for (DataDict exportDataDict : exportDataDictList) {
                    // exportDictKey = exportDataDict.getDictKey();
                    exportDictText = exportDataDict.getDictText();
                    exportFormatDictGroupKey = exportDataDict.getFormatDictGroupKey();

                    // dataDictKeyList.add(exportDictKey);
                    titleList.add(exportDictText);

                    if (!StringUtil.isNullOrSpace(exportFormatDictGroupKey)
                            && !formatDictGroupKeyList.contains(exportFormatDictGroupKey)
                    ) {
                        formatDictGroupKeyList.add(exportFormatDictGroupKey);
                    }
                }
            }

            if (ListUtil.getListSizeWith0(formatDictGroupKeyList) > 0) {
                List<DataDict> dbFormatDataDictList = dataDictService.queryByDictGroupKey(formatDictGroupKeyList);

                if (ListUtil.getListSizeWith0(dbFormatDataDictList) > 0) {
                    String dbDictGroupKey = null;
                    String dbDictKey = null;

                    for (DataDict dbFormatDataDict : dbFormatDataDictList) {
                        dbDictGroupKey = dbFormatDataDict.getDictGroupKey();
                        dbDictKey = dbFormatDataDict.getDictKey();

                        if (!formatDictGroupKeyWithDictKeyMap.containsKey(dbDictGroupKey)) {
                            formatDictGroupKeyWithDictKeyMap.put(dbDictGroupKey, new HashMap<>());
                        }

                        formatDictGroupKeyWithDictKeyMap.get(dbDictGroupKey).put(dbDictKey, dbFormatDataDict);
                    }
                }
            }
            // end 解析导出的数据字典信息

            final PropertyUtilsBean propertyUtilsBean = BeanUtilsBean2.getInstance().getPropertyUtils();

            ExcelCustomResultHandler<Object> excelResultHandler = new ExcelCustomResultHandler<Object>() {
                @Override
                public void handleResult(ResultContext<Object> resultContext, SXSSFWorkbook workbook) {
                    // resultCount
                    // int resultCount = resultContext.getResultCount();
                    // resultObject每一行数据
                    // Object resultObject = resultContext.getResultObject();
                    Object resultObject = resultContext.getResultObject();

                    if (arrSheet[0] == null) {
                        // 创建sheet
                        arrSheet[0] = workbook.createSheet();
                        // 创建row
                        Row row = arrSheet[0].createRow(rowIndex.getAndIncrement());
                        // 创建标题行
                        createTitleRow(row, arrCellStyleTitle, titleList);
                    }

                    // 创建数据数组，元素应该对应titleList
                    List<Object> dataList = new ArrayList<>();

                    if (ListUtil.getListSizeWith0(exportDataDictList) > 0) {
                        try {
                            Map<String, Object> resultMap = propertyUtilsBean.describe(resultObject);
                            String exportDictKey = null;
                            String exportFormatDictGroupKey = null;
                            Object itemResultValue = null;
                            Map<String, DataDict> itemFormatDictGroupKeyMap = null;
                            DataDict formatDataDict = null;
                            boolean formatSuccess = false;

                            for (DataDict exportDataDict : exportDataDictList) {
                                exportDictKey = exportDataDict.getDictKey();
                                exportFormatDictGroupKey = exportDataDict.getFormatDictGroupKey();

                                itemResultValue = resultMap.get(exportDictKey);
                                formatSuccess = false;

                                if (!StringUtil.isNullOrSpace(exportFormatDictGroupKey)) {
                                    itemFormatDictGroupKeyMap = formatDictGroupKeyWithDictKeyMap.get(exportFormatDictGroupKey);

                                    if (itemFormatDictGroupKeyMap != null && itemFormatDictGroupKeyMap.size() > 0) {
                                        formatDataDict = itemFormatDictGroupKeyMap.get(StringUtil.formatObjectToString(itemResultValue));

                                        if (formatDataDict != null) {
                                            formatSuccess = true;
                                            dataList.add(formatDataDict.getDictText());
                                        }
                                    }

                                    if (!formatSuccess) {
                                        dataList.add("dataDictFormatFail:::" + StringUtil.formatObjectToStringLen0(itemResultValue));
                                    }
                                } else {
                                    dataList.add(itemResultValue);
                                }
                            }
                        } catch (Exception e) {
                            LOGGER.error("{}error;", logInfo, e);
                            throw new ScpException(e);
                        }
                    }

                    // 创建row
                    Row row = arrSheet[0].createRow(rowIndex.getAndIncrement());
                    Cell cell = null;

                    // 创建并设置cell
                    for (int aaa = 0; aaa < dataList.size(); aaa++) {
                        cell = row.createCell(aaa);
                        ExcelUtil.setCellValue(cell, dataList.get(aaa), sdf);
                    }
                }
            };

            ExcelCustomTemplateService.createXlsxToWebDir(
                    sqlSession,
                    mappedStatementId,
                    mappedStatementParameter,
                    webLocalFile,
                    excelResultHandler
            );

        } catch (ScpException e) {
            LOGGER.error("{}error;", logInfo, e);
            throw e;
        } catch (Exception e) {
            LOGGER.error("{}error;", logInfo, e);
            // throw new ScpException(e);
            throw e;
        }

        return createFileInfo;
    }

    private static void createTitleRow(Row row, CellStyle[] arrCellStyleTitle, List<String> titleList) {
        if (row != null && ListUtil.getListSizeWith0(titleList) > 0) {
            Cell cell = null;

            if (arrCellStyleTitle[0] == null) {
                Workbook workbook = row.getSheet().getWorkbook();

                // begin 创建字体
                Font fontTitle = workbook.createFont();
                // make it blue
                fontTitle.setColor(Font.COLOR_RED);
                // end 创建字体

                // begin 创建单元格样式
                CellStyle cellStyleTitle = workbook.createCellStyle();
                cellStyleTitle.setFont(fontTitle);
                // end 创建单元格样式

                arrCellStyleTitle[0] = cellStyleTitle;
            }

            // 创建并设置cell
            for (int aaa = 0; aaa < titleList.size(); aaa++) {
                cell = row.createCell(aaa);
                // 设置单元格样式
                if (arrCellStyleTitle[0] != null) {
                    cell.setCellStyle(arrCellStyleTitle[0]);
                }

                ExcelUtil.setCellValue(cell, titleList.get(aaa));
            }
        }
    }

}
